package com.portalEsporteClubeBahia.app.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.portalEsporteClubeBahia.app.ApplicationException;
import com.portalEsporteClubeBahia.app.vo.MediaVO;
import com.portalEsporteClubeBahia.app.vo.TypeMediaVO;

public class MediaDAO extends DAOFactory {

	public MediaDAO() throws ApplicationException {
		super();
	}
	
	public List<MediaVO> findAll() throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		List<MediaVO> list = new ArrayList<MediaVO>();
		
		try{
			StringBuffer sql = new StringBuffer("SELECT TBL_MEDIA.ID, TBL_MEDIA.TITLE, TBL_MEDIA.URLMEDIA, TBL_MEDIA.SHORTCONTENT, TBL_MEDIA.ACTIVE, TBL_MEDIA.DATE, ");
			sql.append(" TBL_TYPE_MEDIA.ID, TBL_TYPE_MEDIA.NAME, TBL_MEDIA.TIME, TBL_MEDIA.RANKING ");
			sql.append(" FROM TBL_MEDIA, TBL_TYPE_MEDIA WHERE TBL_MEDIA.typeMediaId = TBL_TYPE_MEDIA.ID AND TBL_MEDIA.active = 1 ORDER BY tbl_media.DATE DESC ");
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			
			while(rs.next()){
				MediaVO media = new MediaVO();
				media.setId(rs.getLong(1));
				media.setTitle(rs.getString(2));
				media.setUrlMedia(rs.getString(3));
				media.setShortContent(rs.getString(4));
				media.setActive(rs.getBoolean(5));
				media.setDate(rs.getTimestamp(6));
				TypeMediaVO type = new TypeMediaVO();
				type.setId(rs.getShort(7));
				type.setName(rs.getString(8));
				media.setTypeMedia(type);
				media.setTime(rs.getTime(9));
				media.setRanking(rs.getInt(10));
				list.add(media);
			}
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, rs);
		}
				
		return list;
		
	}
	
	public List<TypeMediaVO> findAllTypeMedia() throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		List<TypeMediaVO> list = new ArrayList<TypeMediaVO>();
		
		try{
			StringBuffer sql = new StringBuffer(" SELECT TBL_TYPE_MEDIA.ID, TBL_TYPE_MEDIA.NAME FROM TBL_TYPE_MEDIA ORDER BY TBL_TYPE_MEDIA.NAME ");
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			
			while(rs.next()){
				TypeMediaVO type = new TypeMediaVO();
				type.setId(rs.getShort(1));
				type.setName(rs.getString(2));
				list.add(type);
			}
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, rs);
		}
				
		return list;
		
	}
	
	public void updateRanking(MediaVO media) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		
		StringBuffer sql = new StringBuffer("UPDATE TBL_MEDIA SET RANKING = RANKING + 1 WHERE ID = ?");
		
		try{
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setLong(1, media.getId());
			ps.executeUpdate();
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, null);
		}
		
	}
	
	public void insert(TypeMediaVO type) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		
		StringBuffer sql = new StringBuffer();
		sql.append(" INSERT INTO TBL_TYPE_MEDIA ");
		sql.append(" (NAME) ");
		sql.append(" VALUES ");
		sql.append(" (?) ");
		
		try{
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, type.getName());
			ps.executeUpdate();
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, null);
		}
		
	}
	
	public void update(TypeMediaVO type) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		
		StringBuffer sql = new StringBuffer();
		sql.append(" UPDATE TBL_TYPE_MEDIA ");
		sql.append(" SET NAME = ? ");
		sql.append(" WHERE ");
		sql.append(" ID = ? ");
		
		try{
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, type.getName());
			ps.setShort(2, type.getId());
			ps.executeUpdate();
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, null);
		}
		
	}
	
	public void delete(TypeMediaVO type) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		
		StringBuffer sql = new StringBuffer();
		sql.append(" DELETE FROM TBL_TYPE_MEDIA WHERE ID = ?");
		
		try{
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setShort(1, type.getId());
			ps.executeUpdate();
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, null);
		}
		
	}
	
	public void insert(MediaVO media) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		
		StringBuffer sql = new StringBuffer();
		sql.append(" INSERT INTO TBL_MEDIA ");
		sql.append(" (TYPEMEDIAID, TITLE, URLMEDIA, SHORTCONTENT, ACTIVE, DATE, TIME) ");
		sql.append(" VALUES ");
		sql.append(" (?,?,?,?,?,CURRENT_DATE(),?) ");
		
		try{
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setShort(1, media.getTypeMedia().getId());
			ps.setString(2, media.getTitle());
			ps.setString(3, media.getUrlMedia());
			ps.setString(4, media.getShortContent());
			ps.setBoolean(5, media.getActive());
			ps.setTime(6, media.getTime());
			ps.executeUpdate();
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, null);
		}
		
	}
	
	public void update(MediaVO media) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		
		StringBuffer sql = new StringBuffer();
		sql.append(" UPDATE TBL_MEDIA ");
		sql.append(" SET TYPEMEDIAID=?, TITLE=?, URLMEDIA=?, SHORTCONTENT=?, ACTIVE=?, TIME=? ");
		sql.append(" WHERE ");
		sql.append(" ID = ? ");
		
		try{
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setShort(1, media.getTypeMedia().getId());
			ps.setString(2, media.getTitle());
			ps.setString(3, media.getUrlMedia());
			ps.setString(4, media.getShortContent());
			ps.setBoolean(5, media.getActive());
			ps.setTime(6, media.getTime());
			ps.setLong(7, media.getId());
			ps.executeUpdate();
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, null);
		}
		
	}
	
	public void delete(MediaVO media) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		
		StringBuffer sql = new StringBuffer();
		sql.append(" DELETE FROM TBL_MEDIA WHERE ID = ? ");
		
		try{
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setLong(1, media.getId());
			ps.executeUpdate();
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, null);
		}
		
	}
	
	public List<MediaVO> findByFilter(MediaVO filter) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		List<MediaVO> list = new ArrayList<MediaVO>();
		
		try{
			StringBuffer sql = new StringBuffer(" SELECT TBL_MEDIA.ID, TBL_TYPE_MEDIA.ID, TBL_MEDIA.TITLE, ");
			sql.append(" TBL_MEDIA.DATE, TBL_MEDIA.SHORTCONTENT, TBL_TYPE_MEDIA.NAME ");
			sql.append(" FROM TBL_MEDIA, TBL_TYPE_MEDIA WHERE TBL_MEDIA.TYPEMEDIAID = TBL_TYPE_MEDIA.ID ");
			
			if(filter.getTitle()!=null && !filter.getTitle().equals("")){
				sql.append(" AND TBL_MEDIA.TITLE LIKE '%"+filter.getTitle()+"%' ");
			}
			if(filter.getTypeMedia()!=null && filter.getTypeMedia().getId()!=null && filter.getTypeMedia().getId()!=0){
				sql.append(" AND TBL_MEDIA.TYPEMEDIAID = "+filter.getTypeMedia().getId()+" ");
			}
			if(filter.getShortContent()!=null && !filter.getShortContent().equals("")){
				sql.append(" AND TBL_MEDIA.SHORTCONTENT LIKE '%"+filter.getShortContent()+"%' ");
			}
			if(filter.getUrlMedia()!=null && !filter.getUrlMedia().equals("")){
				sql.append(" AND TBL_MEDIA.URLMEDIA LIKE '%"+filter.getUrlMedia()+"%' ");
			}
			if(filter.getDateStringStartsFormat()!=null && !filter.getDateStringStartsFormat().equalsIgnoreCase("")){
				sql.append(" AND TBL_MEDIA.DATE >= '"+filter.getDateStringStartsFormat()+" 00:00:00' ");
			}
			if(filter.getDateStringEndsFormat()!=null && !filter.getDateStringEndsFormat().equalsIgnoreCase("")){
				sql.append(" AND TBL_MEDIA.DATE <= '"+filter.getDateStringEndsFormat()+" 23:59:59' ");
			}
			sql.append(" AND TBL_MEDIA.ACTIVE = "+filter.getActive());
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			
			while(rs.next()){
				MediaVO media = new MediaVO();
				media.setId(rs.getLong(1));
				media.setTypeMedia(new TypeMediaVO());
				media.getTypeMedia().setId(rs.getShort(2));
				media.setTitle(rs.getString(3));
				media.setDate(rs.getTimestamp(4));
				media.setShortContent(rs.getString(5));
				media.getTypeMedia().setName(rs.getString(6));
				list.add(media);
			}
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, rs);
		}
		
		return list;
		
	}
	
	public MediaVO findById(MediaVO filter) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		MediaVO media = new MediaVO();
		
		try{
			StringBuffer sql = new StringBuffer(" SELECT ID, TYPEMEDIAID, TITLE, URLMEDIA, SHORTCONTENT, ACTIVE, DATE, TIME ");
			sql.append(" FROM TBL_MEDIA WHERE ID = ? ");
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setLong(1, filter.getId());
			rs = ps.executeQuery();
			
			while(rs.next()){
				media.setId(rs.getLong(1));
				media.setTypeMedia(new TypeMediaVO());
				media.getTypeMedia().setId(rs.getShort(2));
				media.setTitle(rs.getString(3));
				media.setUrlMedia(rs.getString(4));
				media.setShortContent(rs.getString(5));
				media.setActive(rs.getBoolean(6));
				media.setDate(rs.getTimestamp(7));
				media.setTime(rs.getTime(8));
			}
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, rs);
		}
		
		return media;
		
	}
	
	public TypeMediaVO findById(TypeMediaVO filter) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		TypeMediaVO type = new TypeMediaVO();
		
		try{
			StringBuffer sql = new StringBuffer(" SELECT ID, NAME FROM TBL_TYPE_MEDIA WHERE ID = ?");
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setShort(1, filter.getId());
			rs = ps.executeQuery();
			
			while(rs.next()){
				type.setId(rs.getShort(1));
				type.setName(rs.getString(2));
			}
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, rs);
		}
		
		return type;
		
	}
	
	public List<TypeMediaVO> findByFilter(TypeMediaVO filter) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		List<TypeMediaVO> list = new ArrayList<TypeMediaVO>();
		
		try{
			StringBuffer sql = new StringBuffer(" SELECT ID, NAME FROM TBL_TYPE_MEDIA ");
			if(filter.getName()!=null && !filter.getName().equalsIgnoreCase("")){
				sql.append(" WHERE NAME LIKE '%"+filter.getName()+"%'");
			}
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			
			while(rs.next()){
				TypeMediaVO type = new TypeMediaVO();
				type.setId(rs.getShort(1));
				type.setName(rs.getString(2));
				list.add(type);
			}
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, rs);
		}
		
		return list;
		
	}

}
